﻿CREATE PROCEDURE vantageexport.s_Phones 
WITH EXECUTE AS 'AFC-HOME\SQLServer'
AS

DECLARE @PhoneFilter sysname SET @PhoneFilter = '(0{5,}|9{5,}|8{5,}|7{5,}|6{5,}|5{5,}|4{5,}|3{5,}|2{5,}|1{5,})'

--UPDATE B SET 
--	PhoneHome =  dbo.CleanPhone(BRIDGE.PhoneHome)
----SELECT BRIDGE.PhoneHome, B.PhoneHome AS Expr1, B.FirstName, B.LastName, BRIDGE.ClientName, B.SSN
--FROM         v_Borrower_Update AS B INNER JOIN
--             vantage.v_Bridge AS BRIDGE WITH(nolock) ON B.SSN = BRIDGE.SSN AND dbo.CleanPhone(B.PhoneHome) <> dbo.CleanPhone(BRIDGE.PhoneHome)
--AND dbo.clr_RegEx_Match(BRIDGE.PhoneHome,@PhoneFilter,1) = 0
--AND LEN(BRIDGE.PhoneHome) > 9
--WHERE     (B.SSN > '0')

--UPDATE B SET 
--	PhoneCell = dbo.CleanPhone(BRIDGE.PhoneMobile)
--FROM         t_Borrower AS B INNER JOIN
--             vantage.v_Bridge AS BRIDGE ON B.SSN = BRIDGE.SSN AND dbo.CleanPhone(B.PhoneCell) <> dbo.CleanPhone(BRIDGE.PhoneMobile)
--AND dbo.clr_RegEx_Match(BRIDGE.PhoneMobile,@PhoneFilter,1) = 0
--AND LEN(BRIDGE.PhoneMobile) > 9
--WHERE     (B.SSN > '0')

--UPDATE B SET 
--	PhoneOther = dbo.CleanPhone(BRIDGE.PhoneOther)
--FROM         t_Borrower AS B INNER JOIN
--             vantage.v_Bridge AS BRIDGE ON B.SSN = BRIDGE.SSN AND dbo.CleanPhone(B.PhoneOther) <> dbo.CleanPhone(BRIDGE.PhoneOther)
--AND dbo.clr_RegEx_Match(BRIDGE.PhoneOther,@PhoneFilter,1) = 0
--AND LEN(BRIDGE.PhoneOther) > 9
--WHERE     (B.SSN > '0')

--UPDATE B SET 
--	PhoneWork = dbo.CleanPhone(BRIDGE.PhoneWork)
----, B.PhoneHome AS Expr1, B.FirstName, B.LastName, BRIDGE.ClientName, B.SSN
--FROM         v_Borrower_Update AS B INNER JOIN
--                      vantage.v_Bridge AS BRIDGE ON B.SSN = BRIDGE.SSN AND dbo.CleanPhone(B.PhoneWork) <> dbo.CleanPhone(BRIDGE.PhoneWork)
--AND dbo.clr_RegEx_Match(BRIDGE.PhoneWork,@PhoneFilter,1) = 0
--AND LEN(BRIDGE.PhoneWork) > 9
--WHERE     (B.SSN > '0')


